SELECT WEEK(`orderDate`) as `week`,
    `offices`.`officeCode` as `office`,
    `productName`,
    MAX(`priceEach`) as `priceEach`
FROM `orders`
RIGHT JOIN `orderdetails`
    ON `orders`.`orderNumber` = `orderdetails`.`orderNumber`
LEFT JOIN `products`
    ON `orderdetails`.`productCode` = `products`.`productCode`
LEFT JOIN `customers`
    ON `orders`.`customerNumber` = `customers`.`customerNumber`
LEFT JOIN `employees`
    ON `customers`.`employeeNumber` = `employees`.`employeeNumber`
LEFT JOIN `offices`
    ON `employees`.`officeCode` = `offices`.`officeCode`
WHERE YEAR(`orderDate`) = 2015
GROUP BY `week`, `offices`.`officeCode`
ORDER BY `week`, `offices`.`officeCode` ASC
                    

...and here?

... are you sure?

... really???

::Dealing with objects and entities

::Access to parents, siblings and child

$purchase = $customer->getPurchases()->last();

foreach($purchase->getDetail() as $detail){

    $category = $detail->getProduct()->getCategory();

    if($category->hasOffer()){
       yield $category->getOffer()->getRules();
    }
}

::Inheritance and Polymorphism

::Memory managment: proxy objects, lazy load

function getPurchaseProductsName(PurchaseInterface $purchase){

    foreach($purchase->getDetail() as $detail){
       yield $detail->getProduct()->getName();
    }
}

$purchase = $customer->getPurchases()->last();

foreach(getPurchaseProductsName($purchase) as $productName){
   echo $productName; <-------- 
}

SELECT `name` FROM `purchase_details` WHERE ....
                    

• Database decoupling
• One only point as database interface
• One API, a lot of drivers
• Transaction management
• Cache managment
• Concurrency control
• Query optimization
• Synchronize mapping structure with db and vice versa
• Filter to prevent SQL injections and data sanitize
• Database error handling
• ...

class CustomersPDOGateway implements CustomersGatewayInterface
{
    private $conn;
    
    public function __construct(\PDO $conn){
        $this->conn = $conn;
    }
    
    public function findAll($limit = 100, $offset = 0)
    {
        $prepareQuery = $this->conn->prepare('SELECT * FROM customers LIMIT :limit');
        $limit = $limit ? $limit : 100;
        $limit .= $offset ? ', ' . $offset : '';
        $prepareQuery->bindValue('limit', $limit, \PDO::PARAM_STR);
        $prepareQuery->execute();
        return $prepareQuery->fetchAll(\PDO::FETCH_ASSOC);
    }
    
    public function find($id)
    {
        $prepareQuery = $this->conn->prepare('SELECT * FROM customers WHERE id = :id');
        $prepareQuery->bindValue('id', $id, \PDO::PARAM_INT);
        $prepareQuery->execute();
        return $prepareQuery->fetch(\PDO::FETCH_ASSOC);
    }
    
    public function insert($name, $lastName)
    {
        $prepareQuery = $this->conn
                ->prepare('INSERT INTO customers (name, last_name) VALUES (:name, :last_name)');
        $prepareQuery->bindValue('name', $name, \PDO::PARAM_STR);
        $prepareQuery->bindValue('last_name', $lastName, \PDO::PARAM_STR);
        $result = $prepareQuery->execute();
        return $result ? $conn->lastInsertId() : false;
    }
    
    //...
}

$conn = new PDO("mysql:dbname=$dbname;host=$host;charset=utf8", $username, $password, $options);

$customersTable = new CustomersPDOGateway($conn);

$newCustomerId = $customersTable->insert('John', 'Smith');

$customersTable->delete($newCustomerId);

$customers = $customersTable->findAll();

foreach($customers as $customer){
    yield $customer['name'] . ' ' . $customer['last_name'];
}

$customer = $customersTable->find(15);

if($customer){
    echo $customer['name'] . ' ' . $customer['last_name'];
}

class CustomerPDOGateway implements CustomerGatewayInterface
{
    private $id;
    private $name;
    private $lastName;
    
    //.. Getters and Setters
    
    static public load(array $rs){
        $customer = new static();
        $customer->setId($rs['id'])
                 ->setName($rs['name'])
                 ->setName($rs['last_name']);
        return $customer;
    }
    
    public function insert(\PDO $conn)
    {
        $prepareQuery = $conn
                ->prepare('INSERT INTO customers (name, last_name) VALUES (:name, :last_name)');
        $prepareQuery->bindValue('name', $this->name, \PDO::PARAM_STR);
        $prepareQuery->bindValue('last_name', $this->lastName, \PDO::PARAM_STR);
        $prepareQuery->execute();
        $this->id = $conn->lastInsertId();
        return $this;
    }
    //...
}

$conn = new PDO("mysql:dbname=$dbname;host=$host;charset=utf8",  $username, $password, $options);

$customer = new CustomerPDOGateway();
$customer->setName('John');
$customer->setLastName('Smith');

$customer->insert($conn);

class CustomerPDOFinder implements CustomerFinder
{
    static private $conn;
    
    static public setConnection(\PDO $conn){
        static::$conn = $conn;
    }
    
    static public find($id)
    {
        $prepareQuery = static::$conn->prepare('SELECT * FROM customers WHERE id = :id');
        $prepareQuery->bindValue('id', $id, \PDO::PARAM_INT);
        $prepareQuery->execute();
        $result = $prepareQuery->fetch(\PDO::FETCH_ASSOC);
        return $result ? CustomerPDOGateway::load($result) : false;
    }
}

$conn = new PDO("mysql:dbname=$dbname;host=$host;charset=utf8",  $username, $password, $options);
CustomerPDOFinder::setConnection($conn);

$customer = CustomerPDOFinder::find(15);

if($customer){  
    echo $customer->getName() . ' ' . $customer->getLastName();
}

$flights = App\Flight::where('active', 1)
            ->orderBy('name', 'desc')
            ->take(10)
            ->get();

$flight = new Flight;
$flight->name = $request->name;
$flight->save();

$flight = App\Flight::find(1);
$flight->name = 'New Flight Name';
$flight->save();

$flight = App\Flight::find(1);
$flight->delete();

class Post extends Model
{
    /**
    * Get the comments for the blog post.
    */
    public function comments()
    {
        return $this->hasMany('App\Comment');
    }
}

class Comment extends Model
{
    /**
    * Get the post that owns the comment.
    */
    public function post()
    {
        return $this->belongsTo('App\Post');
    }
}

// Retrieve all posts that have three or more comments...
$posts = Post::has('comments', '>=', 3)->get();

foreach ($posts as $post) {
    //...
}

$comments = App\Post::find(1)->comments;

foreach ($comments as $comment) {
    //...
}

$comment = new App\Comment(['message' => 'A new comment.']);

$post = App\Post::find(1);

$post->comments()->save($comment);

$productRepository = $entityManager->getRepository('Product');

$products = $productRepository->findAll();

foreach ($products as $product) {
    echo sprintf("-%s\n", $product->getName());
}

$product = new Product();
$product->setName($newProductName);

$entityManager->persist($product);

$entityManager->flush();

echo "Created Product with ID " . $product->getId() . "\n";

$productRepository = $entityManager->getRepository('Product');

$product = $productRepository->findOneByName('Awesome Product');
$product->setName($newProductName);

$entityManager->flush();

echo "Updated Product with ID " . $product->getId() . "\n";

class Product
{
    /**
    * @var int
    */
    protected $id;
    /**
    * @var string
    */
    protected $name;
    
    public function getId()
    {
        return $this->id;
    }
    
    public function getName()
    {
        return $this->name;
    }
    
    public function setName($name)
    {
        $this->name = $name;
    }
}

/**
* @Entity @Table(name="products")
**/
class Product
{
    /** @Id @Column(type="integer") @GeneratedValue **/
    protected $id;
    /** @Column(type="string") **/
    protected $name;
    
    // .. (other code)
}

<doctrine-mapping xmlns="https://doctrine-project.org/schemas/orm/doctrine-mapping">
    <entity name="Product" table="products">
        <id name="id" type="integer">
            <generator strategy="AUTO"></generator>
        </id>

        <field name="name" type="string"></field>
    </entity>
</doctrine-mapping>

Product:
    type: entity
    table: products
    id:
        id:
            type: integer
        generator:
            strategy: AUTO
    fields:
        name:
            type: string

When?